using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.Common;

namespace BlogEngine.Core.Providers
{
    /// <summary>
    /// Manages database connections 
    /// </summary>
    public class SQLManagedConnection
    {
        /// <summary>
        /// An Empty ManagedConnection
        /// </summary>
        internal static SQLManagedConnection Empty
        {
            get { return new SQLManagedConnection(); }
        }

        private DbConnection _Connection;
        private DbTransaction _Transaction;
        private DbCommand _Command;
        private bool _AllowCloseConnection;

        public DbConnection Connection
        {
            get { return _Connection; }
        }

        /// <summary>
        /// For internal use only
        /// </summary>
        private SQLManagedConnection()
        {
        }

        /// <summary>
        /// Gets a Database connection that is defined in the config file (see configuration\connectionStrings area)
        /// </summary>
        /// <param name="name">connection string name as defined in the config file</param>
        public SQLManagedConnection(string name)
        {
            ConnectionStringSettings connectionSettings = ConfigurationManager.ConnectionStrings[name]; 
            DbProviderFactory factory = DbProviderFactories.GetFactory(connectionSettings.ProviderName);
            _Connection = factory.CreateConnection();
            _Connection.ConnectionString = connectionSettings.ConnectionString;
        }

        #region Connection factory

        /// <summary>
        /// Static method to return a NON TRANSACTIONAL DbManagedConnection for the given config connection name
        /// </summary>
        /// <param name="name">connection string name as defined in the config file</param>
        /// <returns>a NON TRANSACTIONAL DbManagedConnection</returns>
        public static SQLManagedConnection GetConnection(string name)
        {
            return new SQLManagedConnection(name);
        }

        /// <summary>
        /// Static method to return a TRANSACTIONAL DbManagedConnection for the given config connection name
        /// </summary>
        /// <param name="name">connection string name as defined in the config file</param>
        /// <returns>an OPEN TRANSACTIONAL DbManagedConnection with a BEGIN TRANSACTION</returns>
        public static SQLManagedConnection GetConnectionForTransaction(string name)
        {
            SQLManagedConnection myManagedConnection = new SQLManagedConnection(name);
            myManagedConnection.BeginTransaction();
            return myManagedConnection;
        }

        #endregion


        #region IsNullOrEmpty

        /// <summary>
        /// Test if the DbManagedConnection is "Empty" or Null
        /// </summary>
        /// <param name="value">a ManagedConnection</param>
        /// <returns>true if "Empty" or Null</returns>
        public static bool IsNullOrEmpty(SQLManagedConnection value)
        {
            if (null == value)
            {
                return true;
            }

            if (null == value.Connection)
            {
                return true;
            }

            return false;
        }

        #endregion


        #region Transaction handling

        /// <summary>
        /// Commits the transaction and closes the connection
        /// </summary>
        public void Commit()
        {
            if (null != _Transaction)
            {
                _Transaction.Commit();
                _Transaction = null;
            }

            if (_Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }
        }

        /// <summary>
        /// Rolls back the transaction and closes the connection
        /// </summary>
        public void Rollback()
        {
            if (null != _Transaction)
            {
                _Transaction.Rollback();
                _Transaction = null;
            }

            if (_Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }
        }

        /// <summary>
        /// opens the connection and begins a transaction
        /// </summary>
        /// <returns></returns>
        private DbTransaction BeginTransaction()
        {
            _Connection.Open();
            _Transaction = _Connection.BeginTransaction();
            return _Transaction;
        }

        #endregion


        #region Stored Procedure Name handling

        /// <summary>
        /// Set the Stored Procedure name that is to be used
        /// </summary>
        /// <param name="storedProcedureName">name of the stored procedure to be called</param>
        /// <returns>DbCommand</returns>
        public DbCommand GetStoredProcCommand(string storedProcedureName)
        {
            _Command = _Connection.CreateCommand();
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = storedProcedureName;
            _Command.Transaction = _Transaction;

            return _Command;
        }

        /// <summary>
        /// Set Sql Text for dynamic sql query
        /// </summary>
        /// <param name="commandText">Sql Text</param>
        /// <returns>DbCommand</returns>
        public DbCommand GetTextCommand(string commandText)
        {
            _Command = _Connection.CreateCommand();
            _Command.CommandType = CommandType.Text;
            _Command.CommandText = commandText;
            _Command.Transaction = _Transaction;

            return _Command;
        }

        /// <summary>
        /// Implements the GetSchema database method
        /// </summary>
        /// <param name="collectionName">Name of collection to be returned.  Null or String.Empty will return a collection of GetSchema collection names</param>
        /// <returns>DataTable</returns>
        public DataTable GetSchema(string collectionName)
        {
            if (_Connection.State == ConnectionState.Closed)
            {
                _Connection.Open();
            }
            if (string.IsNullOrEmpty(collectionName))
            {
                return _Connection.GetSchema();
            }
            else
            {
                return _Connection.GetSchema(collectionName);
            }
        }
        #endregion


        #region Parameter handling

        /// <summary>
        /// Add an empty parameter 
        /// </summary>
        /// <returns>DbParameter</returns>
        public DbParameter AddParameter()
        {
            DbParameter myDbParamter = _Command.CreateParameter();
            _Command.Parameters.Add(myDbParamter);

            return myDbParamter;
        }

        /// <summary>
        /// Add an IN Parameter
        /// </summary>
        /// <param name="name">sql paramter name</param>
        /// <param name="dbType">type of paramter</param>
        /// <param name="value">parameter value</param>
        /// <returns>DbParameter</returns>
        public DbParameter AddInParameter(string name, DbType dbType, object value)
        {
            DbParameter myDbParamter = _Command.CreateParameter();
            myDbParamter.ParameterName = name;
            myDbParamter.DbType = dbType;
            myDbParamter.Direction = ParameterDirection.Input;
            myDbParamter.Value = value;
            _Command.Parameters.Add(myDbParamter);

            return myDbParamter;
        }

        /// <summary>
        /// Add an OUT Parameter
        /// </summary>
        /// <param name="name">sql paramter name</param>
        /// <param name="dbType">type of paramter</param>
        /// <returns>DbParameter</returns>
        public DbParameter AddOutParameter(string name, DbType dbType)
        {
            DbParameter myDbParamter = _Command.CreateParameter();
            myDbParamter.ParameterName = name;
            myDbParamter.DbType = dbType;
            myDbParamter.Direction = ParameterDirection.Output;
            _Command.Parameters.Add(myDbParamter);

            return myDbParamter;
        }

        /// <summary>
        /// Add an IN / OUT Parameter
        /// </summary>
        /// <param name="name">sql paramter name</param>
        /// <param name="dbType">type of paramter</param>
        /// <param name="value">Input Parameter Value</param>
        /// <returns>DbParameter</returns>
        public DbParameter AddInOutParameter(string name, DbType dbType, object value)
        {
            DbParameter myDbParamter = _Command.CreateParameter();
            myDbParamter.ParameterName = name;
            myDbParamter.DbType = dbType;
            myDbParamter.Direction = ParameterDirection.InputOutput;
            myDbParamter.Value = value;
            _Command.Parameters.Add(myDbParamter);

            return myDbParamter;
        }

        /// <summary>
        /// Add a RETURN Parameter
        /// </summary>
        /// <param name="name">sql paramter name</param>
        /// <param name="dbType">type of paramter</param>
        /// <returns>DbParameter</returns>
        public DbParameter AddReturnParameter(string name, DbType dbType)
        {
            DbParameter myDbParamter = _Command.CreateParameter();
            myDbParamter.ParameterName = name;
            myDbParamter.DbType = dbType;
            myDbParamter.Direction = ParameterDirection.ReturnValue;
            _Command.Parameters.Add(myDbParamter);

            return myDbParamter;
        }

        /// <summary>
        /// Gets the value of a returned parameter by parameter name
        /// </summary>
        /// <param name="name">sql paramter name</param>
        /// <returns>Object which may be System.DbNull</returns>
        public object GetParameterValue(string name)
        {
            return _Command.Parameters[name].Value;
        }

        #endregion


        #region Execute handling

        /// <summary>
        /// Executes the DbCommand where there is NO Rowset returned
        /// </summary>
        public void ExecuteNonQuery()
        {
            if (_Connection.State == ConnectionState.Closed)
            {
                _Connection.Open();
            }

            _AllowCloseConnection = true;
            _Command.ExecuteNonQuery();
            _Command.Dispose();
            _Command = null;
        }

        /// <summary>
        /// Executes the DbCommand where there is a rowset returned
        ///     The connection is closed if NON TRANSACTIONAL and when the IDataReader is closed
        /// </summary>
        /// <returns>IDataReader rowset</returns>
        public IDataReader ExecuteReader()
        {
            CheckCommand();
            OpenConnection();

            _AllowCloseConnection = false;

            if (_Transaction == null)
            {
                return _Command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            else
            {
                return _Command.ExecuteReader(CommandBehavior.Default);
            }
        }

        /// <summary>
        /// Executes the DbCommand where a Scalar value is returned
        /// </summary>
        /// <returns>Object which may be System.DbNull</returns>
        public object ExecuteScalar()
        {
            if (_Connection.State == ConnectionState.Closed)
            {
                _Connection.Open();
            }
            _AllowCloseConnection = true;
            return _Command.ExecuteScalar();
        }

        private void CheckCommand()
        {
            if (_Command == null)
            {
                throw new NullReferenceException("_Command");
            }
        }

        #endregion


        #region Open and Close

        private void OpenConnection()
        {
            if (_Connection.State == ConnectionState.Closed)
            {
                _Connection.Open();
            }
        }

        /// <summary>
        /// Closes the database connection if NON TRANSACTIONAL and is not returning a ROWSET (IDataReader) 
        ///     - ROWSET (IDataReader) connections are closed when the IDataReader is closed
        ///     - Transactional connections are closed on COMMIT or ROLLBACK
        /// </summary>
        public void CloseIfNonTransactional()
        {
            if (_Transaction == null && _AllowCloseConnection)
            {
                _Connection.Close();
            }
        }

        #endregion

    }
}
